﻿using System;
using System.Collections.Generic;
using System.Text;

namespace CFSystem.JXC.AccessDAL
{
    using System.ComponentModel;
    using System.Data;
    using System.Data.OleDb;
    using CFSystem.JXC.IDAL;
    using CFSystem.JXC.Model;
    using DBUtility;

    public class ProductDAL : IProductDAL
    {
        #region IProduct 成员

        public IList<ProductInfo> GetAllProducts()
        {
            string sqlSelect = "SELECT * FROM tb_GoodBase";
            return GetProducts(sqlSelect);
        }

        public IList<ProductInfo> SearchProduct(string strSearch)
        {
            string sqlSelect = "SELECT * FROM tb_GoodBase WHERE ID LIKE '"
                            + strSearch + "%' OR Name LIKE '"
                            + strSearch + "%' OR BarCode LIKE '"
                            + strSearch + "%'";
            return GetProducts(sqlSelect);
        }

        public void AddProduct(ProductInfo product)
        {
            string sqlInsert = "INSERT INTO [tb_GoodBase] VALUES ('"
                            + product.ProductID + "', '"
                            + product.ProductName + "', '"
                            + product.BarCode + "', "
                            + product.CatergoryID + ", '"
                            + product.CatergoryName + "', "
                            + product.SpecificationID + ", '"
                            + product.SpecificationName + "', "
                            + product.ColorID + ", '"
                            + product.ColorName + "', "
                            + product.UnitID + ", '"
                            + product.UnitName + "', "
                            + product.BrandID + ", '"
                            + product.BrandName + "', '"
                            + product.PurchasePrice + "', '"
                            + product.SalePrice + "', "
                            + product.StoreNumber + ", '"
                            + product.StorePrice + "', "
                            + product.Point + ", "
                            + product.Discount + ", '"
                            + product.Note + "')";
            int resultCnt = AccessHelper.ExecuteNonQuery(AccessHelper.ConnectionString, CommandType.Text, sqlInsert);
        }

        public void DeleteProduct(string productID)
        {
            string sqlDelete = "DELETE FROM [tb_GoodBase] WHERE [ID] = '" + productID + "'";
            int resultCnt = AccessHelper.ExecuteNonQuery(AccessHelper.ConnectionString, CommandType.Text, sqlDelete);            
        }

        public void UpdateProduct(ProductInfo product)
        {
            string sqlUpdate = "UPDATE [tb_GoodBase] SET [Name] = '" + product.ProductName
                            + "', [BarCode] = '" + product.BarCode
                            + "', [CategoryID] = '" + product.CatergoryID
                            + "', [CategoryName] = '" + product.CatergoryName
                            + "', [SpecificationID] = '" + product.SpecificationID
                            + "', [SpecificationName] = '" + product.SpecificationName
                            + "', [ColorID] = '" + product.ColorID
                            + "', [ColorName] = '" + product.ColorName
                            + "', [UnitID] = '" + product.UnitID
                            + "', [UnitName] = '" + product.UnitName
                            + "', [BrandID] = '" + product.BrandID
                            + "', [BrandName] = '" + product.BrandName
                            + "', [PurchasePrice] = '" + product.PurchasePrice
                            + "', [SalePrice] = '" + product.SalePrice
                            + "', [StoreNumber] = '" + product.StoreNumber
                            + "', [StorePrice] = '" + product.StorePrice
                            + "', [Point] = " + product.Point
                            + ", [Discount] = " + product.Discount
                            + ", [Note] = '" + product.Note
                            + "' WHERE [ID] = '" + product.ProductID + "'";                      
            int resultCnt = AccessHelper.ExecuteNonQuery(AccessHelper.ConnectionString, CommandType.Text, sqlUpdate);
        }

        #endregion

        #region Methods for BLProduct class
        private ProductInfo GetProductFromDataReader(IDataReader dataReader)
        {
            ProductInfo product = new ProductInfo();
            product.ProductID = Common.GetValue(dataReader["ID"]);
            product.ProductName = Common.GetValue(dataReader["Name"]);
            product.BarCode = Common.GetValue(dataReader["BarCode"]);
            product.CatergoryID = Convert.ToInt32(Common.GetValue(dataReader["CategoryID"]));
            product.CatergoryName = Common.GetValue(dataReader["CategoryName"]);
            product.SpecificationID = Convert.ToInt32(Common.GetValue(dataReader["SpecificationID"]));
            product.SpecificationName = Common.GetValue(dataReader["SpecificationName"]);
            product.ColorID = Convert.ToInt32(Common.GetValue(dataReader["ColorID"]));
            product.ColorName = Common.GetValue(dataReader["ColorName"]);
            product.UnitID = Convert.ToInt32(Common.GetValue(dataReader["UnitID"]));
            product.UnitName = Common.GetValue(dataReader["UnitName"]);
            product.BrandID = Convert.ToInt32(Common.GetValue(dataReader["BrandID"]));
            product.BrandName = Common.GetValue(dataReader["BrandName"]);
            product.PurchasePrice = Common.GetValue(dataReader["PurchasePrice"]);
            product.SalePrice = Common.GetValue(dataReader["SalePrice"]);

            #region re-design for db change- by jackie -陈伟，这两个地方是我为了调试注释掉了，估计你也要做修改
            //product.StoreNumber = Convert.ToInt32(Common.GetValue(dataReader["StoreNumber"]));
            //product.StorePrice = Common.GetValue(dataReader["StorePrice"]);
            #endregion
            product.Point = Convert.ToBoolean(Common.GetValue(dataReader["Point"]));
            product.Discount = Convert.ToBoolean(Common.GetValue(dataReader["Discount"]));
            product.Note = Common.GetValue(dataReader["Note"]);

            return product;
        }

        private IList<ProductInfo> GetProducts(string sqlSelect)
        {
            OleDbDataReader dataReader = AccessHelper.ExecuteReader(AccessHelper.ConnectionString, CommandType.Text, sqlSelect);

            IList<ProductInfo> productItems = new BindingList<ProductInfo>();

            if (dataReader.HasRows)
            {
                while (dataReader.Read())
                {
                    productItems.Add(GetProductFromDataReader(dataReader));
                }
                dataReader.Close();
            }
            return productItems;
        }
        #endregion
    }
}
